/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Model;

import com.mysql.jdbc.Connection;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.Collections;
import java.util.LinkedList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author ibrahim
 */
public class Database {
  
    private List<Restaurateur>  restaurateur;
    private List<Restaurant> restaurant;
    private static String resultAction;
    private Connection com;
    
    
    public  Database(){
        this.restaurateur = new LinkedList<Restaurateur>();
        this.restaurant = new LinkedList<Restaurant>();
       
    }
   
    public void connect() throws Exception {
       Connection connection=null;
        if(com!=null) return;
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException ex) {
           throw new Exception("Driver not found");
        }
        String url = "jdbc:mysql://localhost:3306/Restauration";
        com =   (Connection) DriverManager.getConnection(url, "root", "");
    }
    
    public void disconnect(){
        if(com!=null){
            try {
                com.close();
            } catch (SQLException ex) {
                System.out.println("Cannot close connection");
            }
        }
    
   }
    
    public String[] checkLogin(String login, String password) throws SQLException{
        String checkSQL="select * from client where Usename=?";
        PreparedStatement checkStatement = com.prepareStatement(checkSQL);
        checkStatement.setString(1,login);
        ResultSet checkresult = checkStatement.executeQuery();
        while(checkresult.next()){
            String p = checkresult.getString("Password");
            if(p.equals(password)){
                String[] client = { login, 
                    checkresult.getString("FirstName"), 
                    checkresult.getString("LastName"),
                    checkresult.getString("StreetNumber"),
                    checkresult.getString("StreetName"),
                    checkresult.getString("City"),
                    checkresult.getString("PostalCode")};
                return client;
            }
        }
        return null;
    }
     
    public void addCommand(List<OrderedRepas> repas,float total,String date, String adresse, String username) throws SQLException{
        String addSQL="insert into commandes(date,adresse,total,statut,date_carnet,username) values (?,?,?,?,?,?)";
        PreparedStatement addStatement = com.prepareStatement(addSQL);
        addStatement.setString(1,date);
        addStatement.setString(2,adresse);
        addStatement.setFloat(3, total);
        addStatement.setString(4,"recu");
        addStatement.setString(5,"0000-00-00 00:00:00");
        addStatement.setString(6, username);
        addStatement.executeUpdate();
        addStatement.close();
        
        String getSQL="select * from commandes order by numero_commande desc limit 0,1";
        PreparedStatement getStatement = com.prepareStatement(getSQL);
        ResultSet r = getStatement.executeQuery();
        int id=0;
        while(r.next()){
            id = r.getInt("numero_commande");
        }
        
        for(int i=0;i<repas.size();i++){
            String addSQL2="insert into repas_commandes(numero_commande,id_repas,quantite) values (?,?,?)";
            PreparedStatement addStat2 = com.prepareStatement(addSQL2);
            addStat2.setInt(1, id);
            addStat2.setInt(2, repas.get(i).getIdRepas());
            addStat2.setInt(3, repas.get(i).getQuantity());
            addStat2.executeUpdate();
            addStat2.close();
        }
        System.out.println(id);
    }
    
    public void save() throws SQLException{
        
        String checkSQL = "select count(*) as count from restaurateur where ID=?";
        String insertSQL="insert into restaurateur(ID,FirstName,LastName,Expertise,Is_CanadianCitizen,PermanantCode,Gender) values(?,?,?,?,?,?,?)" ;
        String updateSQL="update restaurateur set FirstName=?,LastName=?,Expertise=?,Is_CanadianCitizen=?,PermanantCode=?,Gender=? where ID=?" ;
        PreparedStatement insertStatement = com.prepareStatement(insertSQL);
        PreparedStatement updateStatement = com.prepareStatement(updateSQL);
        
        PreparedStatement checkStat = com.prepareStatement(checkSQL);
        for(Restaurateur rest:this.restaurateur){
            int id = rest.getId();
            String fname = rest.getFname();
            String lname = rest.getLname();
            Expertise expertise = rest.getExpertise();
            boolean canacitizen = rest.isCitizen();
            String permcode = rest.getPermanantCode();
            Gender gender = rest.getGender();
            checkStat.setInt(1, id);
            ResultSet checkresult = checkStat.executeQuery();
            checkresult.next();
            int count  = checkresult.getInt(1);
           
            if(count == 0){
              int col = 1;
              insertStatement.setInt(col++, id);
              insertStatement.setString(col++, fname);
              insertStatement.setString(col++, lname);
              insertStatement.setString(col++, expertise.name());
              insertStatement.setBoolean(col++, canacitizen);
              insertStatement.setString(col++, permcode);
              insertStatement.setString(col++, gender.name());
              insertStatement.executeUpdate();
              
              System.out.println("ID:" + id +  " " + count);  
              Database.setResultAction("Insertion");
              //JOptionPane.showMessageDialog(null,"Item(s) Successfully Added to Database !");
            }
            else {
                System.out.println("Updating person with ID: " + id); 
                int col = 1;
              
              updateStatement.setString(col++, fname);
              updateStatement.setString(col++, lname);
              updateStatement.setString(col++, expertise.name());
              updateStatement.setBoolean(col++, canacitizen);
              updateStatement.setString(col++, permcode);
              updateStatement.setString(col++, gender.name());
              updateStatement.setInt(col++, id);
              updateStatement.executeUpdate();
              Database.setResultAction("Update");
              
              //JOptionPane.showMessageDialog(null,"Item(s) Successfully Updated in Database !");
                 
            }                  
            
            
        }
        updateStatement.close();
        insertStatement.close();
        checkStat.close();
    }
    public void loadFromDatabase() throws SQLException{
      this.restaurateur.clear();
      String sql="select ID,FirstName,LastName,Expertise,Is_CanadianCitizen,PermanantCode,Gender from restaurateur order by LastName";
      Statement selectStatement = com.createStatement();
      ResultSet results = selectStatement.executeQuery(sql);
      while(results.next()){
          int id = results.getInt("ID");
          String fname = results.getString("FirstName");
          String lname = results.getString("LastName");
          String expertise = results.getString("Expertise");
          boolean citizen = results.getBoolean("Is_CanadianCitizen");
          String percode = results.getString("PermanantCode");
          String gender = results.getString("Gender");
          Restaurateur rest = new Restaurateur(fname,lname,Expertise.valueOf(expertise),citizen,percode,Gender.valueOf(gender));
          rest.setId(id);
          this.restaurateur.add(rest);
          
          
          System.out.println(rest.getLname());
      }
      results.close();
      selectStatement.close();
    }
    public void deleteFromDatabase(int id) throws SQLException{
        String deleteSQL = "delete from restaurateur where id="+id;
        PreparedStatement deleteStatement = com.prepareStatement(deleteSQL);
        
        
                deleteStatement.execute();
            
        
        
        deleteStatement.close();
    }
    
    public List<Repas> loadRepas(int id){
        List<Repas> repasList= new LinkedList<Repas>();
        try {
            //Load each menu from one restaurant (based on id)
            String selectSQL = "select * from menu where id_restaurant=" + id;
            PreparedStatement selectStatement = com.prepareStatement(selectSQL);
            ResultSet selectresult = selectStatement.executeQuery();
            while (selectresult.next()) {
                //For each menu
                String selectRepasSQL = "select * from repas where id_menu=" + selectresult.getInt("id");
                PreparedStatement selectRepasStatement = com.prepareStatement(selectRepasSQL);
                ResultSet selectRepasresult = selectRepasStatement.executeQuery();
                while (selectRepasresult.next()){
                    Repas r = new Repas(selectRepasresult.getInt("id"),selectRepasresult.getString("nom"), selectRepasresult.getString("description"), selectRepasresult.getFloat("prix"), selectRepasresult.getInt("id_menu") );
                    repasList.add(r);
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return repasList;
    }
    
    public List<Repas> loadRepasFromMenu(int id) throws SQLException{
        String selectSQL = "select * from repas where id_menu="+id;
        PreparedStatement selectRepasStatement = com.prepareStatement(selectSQL);
        ResultSet res = selectRepasStatement.executeQuery();
        List<Repas> repasList = new LinkedList<Repas>();
        while(res.next()){
            Repas r = new Repas(res.getInt("id"), 
                    res.getString("nom"), 
                    res.getString("description"), 
                    res.getFloat("prix"), 
                    res.getInt("id_menu"));
            repasList.add(r);
        }
        return repasList;
    }
    
    public void addMenu(String name, int idRestaurant) throws SQLException{
        String addSQL = "insert into menu(nom,id_restaurant) values(?,?)" ;
        PreparedStatement addStatement = com.prepareStatement(addSQL);
        addStatement.setString(1,name);
        addStatement.setInt(2, idRestaurant);
        addStatement.executeUpdate();
    }
    
    public void addRepas(String name, String description, float price, int id_menu) throws SQLException{
        String addSQL = "insert into repas(nom, description, prix, id_menu) values (?,?,?,?)";
        PreparedStatement addStatement = com.prepareStatement(addSQL);
        addStatement.setString(1, name);
        addStatement.setString(2, description);
        addStatement.setFloat(3, price);
        addStatement.setInt(4, id_menu);
        addStatement.executeUpdate();
    }
    
    public void modifRepas(String name, String description, float price, int id_menu, int id_repas) throws SQLException{
        String upSQL = "update repas set nom=?,description=?,prix=?,id_menu=? where id=?";
        PreparedStatement upStatement = com.prepareStatement(upSQL);
        upStatement.setString(1, name);
        upStatement.setString(2, description);
        upStatement.setFloat(3, price);
        upStatement.setInt(4, id_menu);
        upStatement.setInt(5, id_repas);
        upStatement.executeUpdate();
    }
    
    
    public void insertRestaurateur(Restaurateur restaurateur){
        this.restaurateur.add(restaurateur);
    }
    public void removeRestaurateur(int index){
        this.restaurateur.remove(index);
    }
    public int getRestaurateurID(int index){
        return this.restaurateur.get(index).getId();
    }
    public List<Restaurateur> getRestaurateurs(){
         return Collections.unmodifiableList(this.restaurateur);
    }
    
    public void saveToFile(File file) throws IOException{
        FileOutputStream fos = new  FileOutputStream(file);
        ObjectOutputStream oos = new ObjectOutputStream(fos);
        Restaurateur[] restaurateurs = restaurateur.toArray(new Restaurateur[restaurateur.size()]);
        oos.writeObject(restaurateurs);
        oos.close();
    }
    
    public void loadFromFile(File file) throws IOException{
        FileInputStream fis = new  FileInputStream(file);
        ObjectInputStream ois = new ObjectInputStream(fis);
        try {
            Restaurateur[] restaurateurs = (Restaurateur[])ois.readObject();
            restaurateur.clear();
            restaurateur.addAll(Arrays.asList(restaurateurs));
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
        ois.close();
    }

    /**
     * @return the resultAction
     */
    public String getResultAction() {
        return resultAction;
    }

    /**
     * @param resultAction the resultAction to set
     */
    private static void setResultAction(String resultAction) {
        Database.resultAction = resultAction;
    }
    /////////////////////////////////////////////////////////////////////////////////////////////////////////////////
    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////
    ///////////////////////////////////////////////////////////////////////////////////////////////////////////////
    ///////////////////////////////////////////////////////////////////////////////////////////////////////////////
    
    public void saveRestaurant() throws SQLException{
        
        String checkSQL = "select count(*) as count from restaurant where ID=?";
        String insertSQL="insert into restaurant(ID,Name,StreetNumber,City,PostalCode,Restaurateur) values(?,?,?,?,?,?)" ;
        String updateSQL="update restaurant set Name=?,StreetNumber=?,City=?,PostalCode=?,Restaurateur=? where ID=?" ;
        PreparedStatement insertStatement = com.prepareStatement(insertSQL);
        PreparedStatement updateStatement = com.prepareStatement(updateSQL);
        
        PreparedStatement checkStat = com.prepareStatement(checkSQL);
        for(Restaurant rest:this.restaurant){
            int id = rest.getId();
            String name = rest.getName();
            int streetNum = rest.getNumStreet();
            String city = rest.getCity();
            String postalCode = rest.getPostalCode();
            String restaurateurr = rest.getRestaurateur();
            checkStat.setInt(1, id);
            ResultSet checkresult = checkStat.executeQuery();
            checkresult.next();
            int count = checkresult.getInt(1);
           
            if(count == 0){
              int col = 1;
              insertStatement.setInt(col++, id);
              insertStatement.setString(col++, name);
              insertStatement.setInt(col++, streetNum);
              insertStatement.setString(col++, city);
              insertStatement.setString(col++, postalCode);
              insertStatement.setString(col++, restaurateurr);
              insertStatement.executeUpdate();
              
              System.out.println("ID:" + id +  " " + count);  
              Database.setResultAction("Insertion");
              
            }
            else {
                System.out.println("Updating person with ID: " + id); 
                int col = 1;
              
              updateStatement.setString(col++, name);
              updateStatement.setInt(col++, streetNum);
              updateStatement.setString(col++, city);
              updateStatement.setString(col++, postalCode);
              updateStatement.setString(col++, restaurateurr);
              updateStatement.setInt(col++, id);
              updateStatement.executeUpdate();
              Database.setResultAction("Update");
              
              //JOptionPane.showMessageDialog(null,"Item(s) Successfully Updated in RestaurateurDatabase !");
                 
            }                  
            
            
        }
        updateStatement.close();
        insertStatement.close();
        checkStat.close();
    }
    public void loadRestaurantFromDatabase() throws SQLException{
      this.restaurant.clear();
      String sql="select ID,Name,StreetNumber,City,PostalCode,Restaurateur from restaurant order by Name";
      Statement selectStatement = com.createStatement();
      ResultSet results = selectStatement.executeQuery(sql);
      while(results.next()){
          int id = results.getInt("ID");
          String name = results.getString("Name");
          int streetnum = results.getInt("StreetNumber");
          String city = results.getString("City");
          String postalCode = results.getString("PostalCode");
          String restaurateur = results.getString("Restaurateur");
          Restaurant rest = new Restaurant(id,name,streetnum,city,postalCode,restaurateur);
          
          this.restaurant.add(rest);
          
          
          System.out.println(rest.getName());
      }
      results.close();
      selectStatement.close();
    }
    public void deleteRestaurantFromDatabase(int id) throws SQLException{
        String deleteSQL = "delete from restaurant where id="+id;
        PreparedStatement deleteStatement = com.prepareStatement(deleteSQL);
        
        
                deleteStatement.execute();
            
        
        
        deleteStatement.close();
    }
    
    public void insertRestaurant(Restaurant restaurant){
        this.restaurant.add(restaurant);
    }
    public void removeRestaurant(int index){
        this.restaurant.remove(index);
    }
    public int getRestaurantID(int index){
        return this.restaurant.get(index).getId();
    }
    public List<Restaurant> getRestaurants(){
         return Collections.unmodifiableList(this.restaurant);
    }
    
    public void saveRestaurantToFile(File file) throws IOException{
        FileOutputStream fos = new  FileOutputStream(file);
        ObjectOutputStream oos = new ObjectOutputStream(fos);
        Restaurant[] restaurants = restaurant.toArray(new Restaurant[restaurant.size()]);
        oos.writeObject(restaurants);
        oos.close();
    }
    
    public void loadRestaurantFromFile(File file) throws IOException{
        FileInputStream fis = new  FileInputStream(file);
        ObjectInputStream ois = new ObjectInputStream(fis);
        try {
            Restaurant[] restaurants = (Restaurant[])ois.readObject();
            restaurant.clear();
            restaurant.addAll(Arrays.asList(restaurants));
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
        ois.close();
    }
    
    public List<Menu> loadMenus(int idRestaurant) throws SQLException{
        String getSQL= "select * from menu where id_restaurant="+idRestaurant;
        PreparedStatement getStatement = com.prepareStatement(getSQL);
        ResultSet res = getStatement.executeQuery();
        List<Menu> menus = new LinkedList<Menu>();
        
        while(res.next()){
            Menu m = new Menu(res.getInt("id"), res.getString("nom"), res.getInt("id_restaurant"));
            menus.add(m);
        }
        
        
        return menus;
    }
}
